// 数据库性能监控和索引优化
import { performance } from 'perf_hooks';

class DatabaseMonitor {
  constructor() {
    this.metrics = {
      queries: new Map(),
      connections: {
        postgres: { active: 0, idle: 0, waiting: 0 },
        redis: { connected: false }
      },
      performance: {
        avgQueryTime: 0,
        slowQueries: 0,
        cacheHitRate: 0
      }
    };
    
    this.monitoringInterval = null;
    this.slowQueryThreshold = 500; // 0.5秒
  }

  // 启动监控
  startMonitoring(intervalMs = 30000) {
    console.log('🔍 启动数据库性能监控...');
    
    this.monitoringInterval = setInterval(() => {
      this.collectMetrics();
      this.checkAlerts();
    }, intervalMs);
  }

  // 停止监控
  stopMonitoring() {
    if (this.monitoringInterval) {
      clearInterval(this.monitoringInterval);
      console.log('🛑 停止数据库性能监控');
    }
  }

  // 收集指标
  async collectMetrics() {
    try {
      // 收集PostgreSQL连接池指标
      if (global.postgresPool) {
        this.metrics.connections.postgres = {
          active: global.postgresPool.totalCount - global.postgresPool.idleCount,
          idle: global.postgresPool.idleCount,
          waiting: global.postgresPool.waitingCount
        };
      }

      // 收集Redis连接状态
      if (global.redisClient) {
        this.metrics.connections.redis.connected = global.redisClient.isOpen;
      }

      // 计算性能指标
      this.calculatePerformanceMetrics();

    } catch (error) {
      console.error('❌ 收集数据库指标失败:', error);
    }
  }

  // 计算性能指标
  calculatePerformanceMetrics() {
    const queries = Array.from(this.metrics.queries.values());
    
    if (queries.length > 0) {
      const totalTime = queries.reduce((sum, q) => sum + q.totalTime, 0);
      this.metrics.performance.avgQueryTime = totalTime / queries.length;
      
      this.metrics.performance.slowQueries = queries.filter(q => 
        q.avgTime > this.slowQueryThreshold
      ).length;
      
      const totalHits = queries.reduce((sum, q) => sum + q.hits, 0);
      const totalMisses = queries.reduce((sum, q) => sum + q.misses, 0);
      const totalRequests = totalHits + totalMisses;
      
      this.metrics.performance.cacheHitRate = totalRequests > 0 
        ? (totalHits / totalRequests) * 100 
        : 0;
    }
  }

  // 记录查询性能
  recordQuery(queryName, executionTime, isCached = false) {
    if (!this.metrics.queries.has(queryName)) {
      this.metrics.queries.set(queryName, {
        count: 0,
        totalTime: 0,
        avgTime: 0,
        hits: 0,
        misses: 0,
        lastExecuted: null
      });
    }

    const queryStats = this.metrics.queries.get(queryName);
    queryStats.count++;
    queryStats.totalTime += executionTime;
    queryStats.avgTime = queryStats.totalTime / queryStats.count;
    queryStats.lastExecuted = new Date();

    if (isCached) {
      queryStats.hits++;
    } else {
      queryStats.misses++;
    }

    // 慢查询警告
    if (executionTime > this.slowQueryThreshold) {
      this.alertSlowQuery(queryName, executionTime);
    }
  }

  // 慢查询告警
  alertSlowQuery(queryName, executionTime) {
    console.warn(`🐌 慢查询检测: ${queryName}`, {
      executionTime: executionTime.toFixed(2) + 'ms',
      threshold: this.slowQueryThreshold + 'ms',
      timestamp: new Date().toISOString()
    });

    // 这里可以集成到告警系统
    this.sendAlert({
      type: 'SLOW_QUERY',
      query: queryName,
      executionTime,
      severity: executionTime > 2000 ? 'HIGH' : 'MEDIUM'
    });
  }

  // 发送告警
  sendAlert(alert) {
    // 集成到外部告警系统（如Slack、邮件等）
    console.log('🚨 数据库告警:', alert);
  }

  // 检查告警条件
  checkAlerts() {
    const { connections, performance } = this.metrics;

    // 连接池告警
    if (connections.postgres.waiting > 10) {
      this.sendAlert({
        type: 'HIGH_WAITING_CONNECTIONS',
        count: connections.postgres.waiting,
        severity: 'HIGH'
      });
    }

    if (connections.postgres.idle === 0 && connections.postgres.active > 0) {
      this.sendAlert({
        type: 'NO_IDLE_CONNECTIONS',
        severity: 'MEDIUM'
      });
    }

    // 性能告警
    if (performance.avgQueryTime > 1000) {
      this.sendAlert({
        type: 'HIGH_AVG_QUERY_TIME',
        avgTime: performance.avgQueryTime,
        severity: 'HIGH'
      });
    }

    if (performance.cacheHitRate < 50) {
      this.sendAlert({
        type: 'LOW_CACHE_HIT_RATE',
        hitRate: performance.cacheHitRate,
        severity: 'MEDIUM'
      });
    }
  }

  // 获取性能报告
  getPerformanceReport() {
    const slowQueries = Array.from(this.metrics.queries.entries())
      .filter(([_, stats]) => stats.avgTime > this.slowQueryThreshold)
      .sort((a, b) => b[1].avgTime - a[1].avgTime)
      .slice(0, 10);

    return {
      summary: {
        totalQueries: Array.from(this.metrics.queries.values()).reduce((sum, q) => sum + q.count, 0),
        slowQueries: this.metrics.performance.slowQueries,
        avgQueryTime: this.metrics.performance.avgQueryTime.toFixed(2) + 'ms',
        cacheHitRate: this.metrics.performance.cacheHitRate.toFixed(2) + '%'
      },
      connections: this.metrics.connections,
      topSlowQueries: slowQueries.map(([name, stats]) => ({
        query: name,
        avgTime: stats.avgTime.toFixed(2) + 'ms',
        count: stats.count,
        lastExecuted: stats.lastExecuted
      })),
      recommendations: this.generateRecommendations()
    };
  }

  // 生成优化建议
  generateRecommendations() {
    const recommendations = [];
    const { connections, performance } = this.metrics;

    // 连接池建议
    if (connections.postgres.waiting > 5) {
      recommendations.push({
        type: 'CONNECTION_POOL',
        message: '连接池等待连接过多，建议增加最大连接数',
        priority: 'HIGH'
      });
    }

    if (performance.cacheHitRate < 60) {
      recommendations.push({
        type: 'CACHE_OPTIMIZATION',
        message: '缓存命中率较低，建议优化缓存策略',
        priority: 'MEDIUM'
      });
    }

    // 慢查询建议
    const slowQueries = Array.from(this.metrics.queries.entries())
      .filter(([_, stats]) => stats.avgTime > 1000);

    if (slowQueries.length > 0) {
      recommendations.push({
        type: 'QUERY_OPTIMIZATION',
        message: `检测到 ${slowQueries.length} 个慢查询，建议优化`, 
        priority: 'HIGH',
        details: slowQueries.map(([name]) => name)
      });
    }

    return recommendations;
  }

  // 重置指标
  resetMetrics() {
    this.metrics.queries.clear();
    this.metrics.performance = {
      avgQueryTime: 0,
      slowQueries: 0,
      cacheHitRate: 0
    };
  }
}

// 索引优化工具
class IndexOptimizer {
  constructor() {
    this.indexRecommendations = new Map();
  }

  // 分析查询模式并推荐索引
  analyzeQueryForIndex(query, executionPlan, tableStats) {
    const recommendations = [];

    // 分析WHERE条件
    const whereConditions = this.extractWhereConditions(query);
    
    for (const condition of whereConditions) {
      if (this.shouldIndexColumn(condition.column, tableStats)) {
        recommendations.push({
          type: 'WHERE_INDEX',
          column: condition.column,
          condition: condition.operator,
          priority: this.calculateIndexPriority(condition, tableStats)
        });
      }
    }

    // 分析JOIN条件
    const joinConditions = this.extractJoinConditions(query);
    
    for (const condition of joinConditions) {
      recommendations.push({
        type: 'JOIN_INDEX',
        table: condition.table,
        column: condition.column,
        priority: 'HIGH'
      });
    }

    // 分析ORDER BY
    const orderByColumns = this.extractOrderByColumns(query);
    
    for (const column of orderByColumns) {
      recommendations.push({
        type: 'ORDER_BY_INDEX',
        column: column,
        priority: 'MEDIUM'
      });
    }

    return recommendations;
  }

  // 提取WHERE条件
  extractWhereConditions(query) {
    const conditions = [];
    const whereMatch = query.match(/WHERE\s+(.+?)(?:\s+(?:ORDER BY|GROUP BY|LIMIT|$))/i);
    
    if (whereMatch) {
      const whereClause = whereMatch[1];
      // 简单的条件解析（实际实现需要更复杂的解析）
      const conditionPattern = /(\w+)\s*(=|>|<|>=|<=|LIKE|IN)\s*([^\s]+)/g;
      let match;
      
      while ((match = conditionPattern.exec(whereClause)) !== null) {
        conditions.push({
          column: match[1],
          operator: match[2],
          value: match[3]
        });
      }
    }
    
    return conditions;
  }

  // 提取JOIN条件
  extractJoinConditions(query) {
    const conditions = [];
    const joinMatches = query.matchAll(/(?:INNER|LEFT|RIGHT|FULL)\s+JOIN\s+(\w+)\s+ON\s+(.+?)(?=\s+(?:JOIN|WHERE|ORDER BY|GROUP BY|$))/gi);
    
    for (const match of joinMatches) {
      const onClause = match[2];
      const columnMatch = onClause.match(/(\w+)\.(\w+)\s*=\s*(\w+)\.(\w+)/);
      
      if (columnMatch) {
        conditions.push({
          table: match[1],
          column: columnMatch[2]
        });
      }
    }
    
    return conditions;
  }

  // 提取ORDER BY列
  extractOrderByColumns(query) {
    const columns = [];
    const orderByMatch = query.match(/ORDER BY\s+(.+?)(?:\s+(?:LIMIT|$))/i);
    
    if (orderByMatch) {
      const orderClause = orderByMatch[1];
      const columnPattern = /(\w+)(?:\s+(?:ASC|DESC))?/g;
      let match;
      
      while ((match = columnPattern.exec(orderClause)) !== null) {
        columns.push(match[1]);
      }
    }
    
    return columns;
  }

  // 判断是否应该索引该列
  shouldIndexColumn(column, tableStats) {
    // 基于统计信息判断索引价值
    if (!tableStats || !tableStats[column]) return true;
    
    const stats = tableStats[column];
    
    // 高基数列更适合索引
    if (stats.cardinality > 0.1) {
      return true;
    }
    
    // 经常用于查询的列
    if (stats.queryFrequency > 0.5) {
      return true;
    }
    
    return false;
  }

  // 计算索引优先级
  calculateIndexPriority(condition, tableStats) {
    let priority = 'LOW';
    
    if (condition.operator === '=') {
      priority = 'HIGH';
    } else if (['>', '<', '>=', '<='].includes(condition.operator)) {
      priority = 'MEDIUM';
    } else if (condition.operator === 'LIKE' && !condition.value.startsWith('%')) {
      priority = 'MEDIUM';
    }
    
    // 基于使用频率调整优先级
    if (tableStats && tableStats[condition.column]) {
      if (tableStats[condition.column].queryFrequency > 0.7) {
        priority = 'HIGH';
      }
    }
    
    return priority;
  }

  // 生成索引创建语句
  generateIndexSQL(table, columns, indexType = 'BTREE', unique = false) {
    const columnList = Array.isArray(columns) ? columns.join(', ') : columns;
    const uniqueKeyword = unique ? 'UNIQUE ' : '';
    const indexName = `idx_${table}_${columns.replace(/[^a-zA-Z0-9]/g, '_')}`;
    
    return `CREATE ${uniqueKeyword}INDEX ${indexName} ON ${table} USING ${indexType} (${columnList});`;
  }
}

// 导出监控工具
const databaseMonitor = new DatabaseMonitor();
const indexOptimizer = new IndexOptimizer();

export {
  databaseMonitor,
  indexOptimizer,
  DatabaseMonitor,
  IndexOptimizer
};